﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    private SqlDataAdapter sdAdapter;
    private SqlConnection conn;

	public DataAccess()
	{
		//
		// TODO: Add constructor logic here
		//
        sdAdapter = new SqlDataAdapter();
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connConfig-khuetm"].ConnectionString);
	}

    public SqlConnection GetConn()
    {
        if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        return conn;
    }

    public DataSet executeSelectQuery(String query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        DataSet ds = new DataSet();
        try
        {
            myCommand.Connection = GetConn();
            myCommand.CommandText = query;
            myCommand.Parameters.AddRange(sqlParameter);
            myCommand.ExecuteNonQuery();
            sdAdapter.SelectCommand = myCommand;
            sdAdapter.Fill(ds);
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {

        }
        return ds;
    }

    public bool executeInsertQuery(String query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        try
        {
            myCommand.Connection = GetConn();
            myCommand.CommandText = query;
            myCommand.Parameters.AddRange(sqlParameter);
            sdAdapter.InsertCommand = myCommand;
            myCommand.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeInsertQuery - Query: " + query + " \nException: \n" + e.StackTrace.ToString());
            return false;
        }
        finally
        {
        }
        return true;
    }

    public bool executeDeleteQuery(String query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        try
        {
            myCommand.Connection = GetConn();
            myCommand.CommandText = query;
            myCommand.Parameters.AddRange(sqlParameter);
            sdAdapter.DeleteCommand = myCommand;
            myCommand.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeDeleteQuery - Query: " + query + " \nException: \n" + e.StackTrace.ToString());
            return false;
        }
        finally
        {
        }
        return true;
    }

    public bool executeUpdateQuery(String query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        try
        {
            myCommand.Connection = GetConn();
            myCommand.CommandText = query;
            myCommand.Parameters.AddRange(sqlParameter);
            sdAdapter.UpdateCommand = myCommand;
            myCommand.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeUpdateQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
            return false;
        }
        finally
        {
        }
        return true;
    }

    public DataSet ExecuteNonquery(string query) {
        SqlCommand myCommand = new SqlCommand();
        DataSet ds = new DataSet();
        try
        {
            myCommand.Connection = GetConn();
            myCommand.CommandText = query;            
            myCommand.ExecuteNonQuery();
            sdAdapter.SelectCommand = myCommand;
            sdAdapter.Fill(ds);
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {

        }
        return ds;
    }

    public DataTable GetAllRecord(string strComm)
    {
        try
        {
            
            SqlDataAdapter sqlDa = new SqlDataAdapter(strComm, conn);
            DataTable dt = new DataTable();
            sqlDa.Fill(dt);
            CloseConnection(conn);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private void CloseConnection(SqlConnection sqlConn)
    {
        try
        {
            if (sqlConn.State != ConnectionState.Closed)
            {
                sqlConn.Close();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

}